﻿using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Configuration.Statements;
using IBatisNet.DataMapper.MappedStatements;
using IBatisNet.DataMapper.Scope;
using Janus.BaseEntitys;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using Janus.Cores.Extension;
using System.Collections;
using Janus.Cores.Repositories;

namespace Janus.Mes.Repositories.iBatis
{
    /// <summary>
    /// 批量操作数据
    /// </summary>
    public class AdoBatcher
    {
        public AdoBatcher(ISqlMapper sqlMap)
        {
            SqlMap = sqlMap;
            ConnectionString = sqlMap.DataSource.ConnectionString;
        }
        protected static ISqlMapper SqlMap = null;// MyIbatisNet.SqlMap;

        protected static string ConnectionString { get; set; }
        /// <summary>
        /// 获取运行的时候sql语句
        /// </summary>
        /// <param name="sqlMap">ISqlMapper</param>
        /// <param name="stateMentName">statement的id</param>
        /// <param name="paramObject">sql语句的参数</param>
        /// <returns>生成的sql语句</returns>
        public virtual string GetSql(string stateMentName, object paramObject)
        {
            string resultsql = string.Empty;
            try
            {
                IMappedStatement statement = SqlMap.GetMappedStatement(stateMentName);
                if (!SqlMap.IsSessionStarted)
                {
                    SqlMap.OpenConnection();
                }
                RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, SqlMap.LocalSession);
                resultsql = scope.PreparedStatement.PreparedSql;
            }
            catch (Exception ex)
            {
                resultsql = "获取SQL语句出现异常:" + ex.Message;
            }
            return resultsql;
        }
        /// <summary>
        /// 获取DbCommand
        /// </summary>
        /// <param name="sqlMapper">ISqlMapper</param>
        /// <param name="statementName">statement的id</param>
        /// <param name="paramObject">sql语句的参数</param>
        /// <returns>DbCommand</returns>
        protected virtual IDbCommand GetDbCommand(string statementName, object paramObject)
        {
            IStatement statement = SqlMap.GetMappedStatement(statementName).Statement;
            IMappedStatement mapStatement = SqlMap.GetMappedStatement(statementName);
            ISqlMapSession session = new SqlMapSession(SqlMap);

            if (SqlMap.LocalSession != null)
            {
                session = SqlMap.LocalSession;
            }
            else
            {
                session = SqlMap.OpenConnection();
            }

            RequestScope request = statement.Sql.GetRequestScope(mapStatement, paramObject, session);
            mapStatement.PreparedCommand.Create(request, session as ISqlMapSession, statement, paramObject);
            IDbCommand cmd = session.CreateCommand(CommandType.Text);
            cmd.CommandText = request.IDbCommand.CommandText;
            return cmd;
        }
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="sqlMapper">ISqlMapper</param>
        /// <param name="statementName">statement的id</param>
        /// <param name="paramObject">sql语句的参数</param>
        /// <returns>DataTable</returns>
        protected virtual DataSet QueryForDataSet(string statementName, object paramObject)
        {
            DataSet ds = new DataSet();
            IMappedStatement statement = SqlMap.GetMappedStatement(statementName);
            if (!SqlMap.IsSessionStarted)
            {
                SqlMap.OpenConnection();
            }
            RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, SqlMap.LocalSession);
            statement.PreparedCommand.Create(scope, SqlMap.LocalSession, statement.Statement, paramObject);
            IDbCommand cmd = GetDbCommand(statementName, paramObject);//SQL text command
            SqlMap.LocalSession.CreateDataAdapter(cmd).Fill(ds);
            return ds;
        }
        /// <summary>
        /// 执行插入命令
        /// </summary>
        /// <param name="connStr">sql连接字符串</param>
        /// <param name="tableName">表名称</param>
        /// <param name="sourcedt">组装好的要批量导入的datatable</param>
        /// <returns></returns>
        protected virtual bool ExecuteInsertCommand(string connStr, string tableName, DataTable sourcedt)
        {
            bool flag = false;
            try
            {
                using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
                {
                    using (SqlConnection conn = new SqlConnection(connStr))
                    {
                        conn.Open();
                        SqlCommand commandPub = conn.CreateCommand();
                        using (commandPub)
                        {
                            commandPub.CommandText = "SELECT * FROM  " + tableName + " WHERE 1<>1";
                            commandPub.CommandType = System.Data.CommandType.Text;
                            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(commandPub);
                            DataTable tagerTb = new DataTable();
                            sqlDataAdapter.Fill(tagerTb);
                            using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
                            {
                                sbc.DestinationTableName = tableName;
                                sbc.BatchSize = 50000;
                                sbc.BulkCopyTimeout = 180;
                                foreach (DataColumn col in tagerTb.Columns)
                                {
                                    sbc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                                }
                                //for (int i = 0; i < sourcedt.Columns.Count; i++)
                                //{
                                //    sbc.ColumnMappings.Add(sourcedt.Columns[i].ColumnName, i);
                                //}
                                sbc.WriteToServer(sourcedt);
                                flag = true;
                                scope.Complete();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        private DataTable CreateTable(Dictionary<string, Type> listColumns)
        {
            DataTable dt = new DataTable();
            foreach (var item in listColumns)
            {
                dt.Columns.Add(new DataColumn(item.Key, item.Value));
            }
            return dt;
        }
        private static Dictionary<string, Type> CreateModelProperty<T>(T obj) where T : class
        {
            Dictionary<string, Type> listColumns = new Dictionary<string, Type>();
            BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;
            Type objType = typeof(T);
            PropertyInfo[] propInfoArr = objType.GetProperties(bf);
            foreach (PropertyInfo item in propInfoArr)
            {
                //object[] objAttrs = item.GetCustomAttributes(typeof(TableColumnAttribute), true);
                //if (objAttrs != null && objAttrs.Length > 0)//取出实体对应表的实际列名
                {
                    // var sqlDbType = DataType2Clr.Clr2SqlType(item.PropertyType);
                    listColumns.Add(item.Name, item.PropertyType);
                }
            }
            return listColumns;
        }
        /// <summary>
        /// 将泛型变成DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="listModels">实体集合</param>
        /// <returns>转换后的DataTable</returns>
        public DataTable CreateTable<T>(IList<T> listModels) where T : class
        {
            T model = default(T);
            var listProperties = CreateModelProperty<T>(model);
            DataTable dataTable = CreateTable(listProperties);
            BindingFlags bf = BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Static;
            Type objType = typeof(T);
            PropertyInfo[] propInfoArr = objType.GetProperties(bf);
            foreach (T itemModel in listModels)
            {
                DataRow dataRow = dataTable.NewRow();
                foreach (PropertyInfo item in propInfoArr)
                {
                    string propName = item.Name;
                    if (listProperties.ContainsKey(propName))
                    {
                        object value = item.GetValue(itemModel, null);
                        dataRow[propName] = value;
                    }
                }
                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }
        /// <summary>
        /// 外部调用的批量插入的代码
        /// </summary>
        /// <typeparam name="M"></typeparam>
        /// <param name="listModels">泛型集合</param>
        /// <returns>是否插入成功</returns>
        public bool BatchInsert<M>(IList<M> listModels) where M : Entity
        {
            bool flag = false;
            try
            {
                //string connStr = connectionString;
                string tbName = typeof(M).Name;
                //DataTable dt = CreateTable<M>(listModels);
                DataTable dt = Table2ListHelper.ToDataTable<M>(listModels);
                flag = ExecuteInsertCommand(ConnectionString, tbName, dt);
            }
            catch (Exception ex)
            {
                flag = false;
            }
            return flag;
        }

        /// <summary>
        /// 批量更新
        /// </summary>
        /// <typeparam name="M"></typeparam>
        /// <param name="listModels"></param>
        /// <returns></returns>
        public bool BatchUpdate<M>(IList<M> listModels) where M : Entity
        {
            bool result = false;
            //string connString = SqlMap.DataSource.ConnectionString;
            string tbName = typeof(M).Name;
            //DataTable table = CreateTable<M>(listModels);
            DataTable table = Table2ListHelper.ToDataTable<M>(listModels);
            //DataSet ds = new DataSet();
            //ds.Tables.Add(table);
            //var s = SaveDataSet(ds, tbName);
            //return s > 0;
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand comm = conn.CreateCommand();
            comm.CommandTimeout = 180;
            comm.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);
            commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
            try
            {
                conn.Open();
                //设置批量更新的每次处理条数
                adapter.UpdateBatchSize = 5000;
                adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////开始事务
                if (table.ExtendedProperties["SQL"] != null)
                {
                    adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();
                }
                else
                {
                    adapter.SelectCommand.CommandText = "SELECT * FROM " + tbName;
                }
                adapter.InsertCommand = commandBulider.GetInsertCommand();
                adapter.UpdateCommand = commandBulider.GetUpdateCommand();
                adapter.DeleteCommand = commandBulider.GetDeleteCommand();
                //var paramters = adapter.UpdateCommand.Parameters;
                //foreach (SqlParameter p in paramters)
                //{
                //    p.Value=
                //}
                //adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                //var count = adapter.Update(table);
                //table.AcceptChanges();
                int count = 0;
                foreach (DataRow row in table.Rows)
                {
                    if (row.RowState == DataRowState.Added)
                    {
                        foreach (SqlParameter p in adapter.InsertCommand.Parameters)
                        {
                            p.Value = row[p.SourceColumn];                           
                        }
                        count += adapter.InsertCommand.ExecuteNonQuery();
                    }
                    else if (row.RowState == DataRowState.Deleted)
                    {
                        foreach (SqlParameter p in adapter.DeleteCommand.Parameters)
                        {
                            p.Value = row[p.SourceColumn];                           
                        }
                        count += adapter.DeleteCommand.ExecuteNonQuery();
                    }
                    else {
                        foreach (SqlParameter p in adapter.UpdateCommand.Parameters)
                        {
                            p.Value = row[p.SourceColumn];                           
                        }
                        count += adapter.UpdateCommand.ExecuteNonQuery();
                    }
                }
                adapter.SelectCommand.Transaction.Commit();/////提交事务
                result = count > 0;
            }
            catch (Exception ex)
            {
                if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)
                {
                    adapter.SelectCommand.Transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return result;
        }


        public bool BatchSave<M>(IList<M> listModels) where M : Entity
        {
            bool result = false;
            if (listModels.HasValues())
            {
                var insertList = listModels.Where(c => c.RowState == DataRowState.Added || c.RowState == 0).ToList();
                if (insertList.HasValues())
                {
                    result = BatchInsert(insertList);
                }
                if (result)
                {
                    var updateList = listModels.Where(c => c.RowState == DataRowState.Modified).ToList();
                    if (updateList.HasValues())
                    {
                        result = BatchInsert(updateList);
                    }
                }
            }
            return result;
        }

        #region 以DataSet方式更新数据
        /// <summary>
        /// 使用表别名，自动保存DataSet。
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static int SaveDataSet(DataSet ds, string tableName)
        {
            int rows = 0;
            SqlConnection myConn = null;
            try
            {
                using (myConn = new SqlConnection(ConnectionString))
                {
                    SqlDataAdapter myAdapter = new SqlDataAdapter();
                    SqlCommand myCommand = new SqlCommand("select * from " + tableName, myConn);
                    myAdapter.SelectCommand = myCommand;
                    SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                    myCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                    rows = myAdapter.Update(ds, tableName);
                    ds.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                rows = 0;
                throw ex;
            }
            return rows;
        }
        /// <summary>
        /// 使用表别名，自动保存DataSet。
        /// 使用此函数时必须为每个DataTable都设置别名。
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static int SaveDataSet(DataSet ds, List<string> tabeNames)
        {
            int rows = 0;
            SqlConnection myConn = null;
            SqlTransaction myTrans = null;
            try
            {
                using (myConn = new SqlConnection(ConnectionString))
                {
                    if (myConn.State == ConnectionState.Closed)
                        myConn.Open();
                    myTrans = myConn.BeginTransaction();
                    SqlDataAdapter myAdapter = new SqlDataAdapter();
                    foreach (string tableName in tabeNames)
                    {
                        SqlCommand myCommand = new SqlCommand("select * from " + tableName, myConn);
                        myAdapter.SelectCommand = myCommand;
                        myAdapter.SelectCommand.Transaction = myTrans;
                        SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                        myCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                        rows += myAdapter.Update(ds, tableName);
                    }
                    myTrans.Commit();
                    ds.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                rows = 0;
                throw ex;
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                    myConn.Close();
            }
            return rows;
        }
        /// <summary>
        /// 使用表别名，自动保存DataSet。
        /// 使用此函数时必须为每个DataTable都设置别名。
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static int SaveDataSet(DataSet ds, Dictionary<string, string> tabeNames)
        {
            int rows = 0;
            SqlConnection myConn = null;
            SqlTransaction myTrans = null;
            SqlCommand myCommand = null;
            try
            {
                myConn = new SqlConnection(ConnectionString);
                if (myConn.State == ConnectionState.Closed)
                    myConn.Open();
                myTrans = myConn.BeginTransaction();
                SqlDataAdapter myAdapter = new SqlDataAdapter();
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    if (tabeNames.ContainsKey(ds.Tables[i].TableName))
                    {
                        string _expCols = tabeNames[ds.Tables[i].TableName];
                        if (string.IsNullOrEmpty(_expCols))
                        {
                            myCommand = new SqlCommand("select * from " + ds.Tables[i].TableName, myConn);
                            myAdapter.SelectCommand = myCommand;
                            myAdapter.SelectCommand.Transaction = myTrans;
                            SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                            myCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                            rows += myAdapter.Update(ds, ds.Tables[i].TableName);
                        }
                        else
                        {
                            StringBuilder sb = new StringBuilder();
                            foreach (DataColumn col in ds.Tables[i].Columns)
                            {
                                if (!_expCols.Split(new char[] { ',' }).ToArray<string>().Contains(col.ColumnName))
                                    sb.Append(col.ColumnName + ",");
                            }
                            myCommand = new SqlCommand();
                            myCommand.CommandText = "Select " + sb.ToString().TrimEnd(new char[] { ',' }) + " from " + ds.Tables[i].TableName;
                            myCommand.Connection = myConn;
                            myAdapter.SelectCommand = myCommand;
                            myAdapter.SelectCommand.Transaction = myTrans;
                            SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                            myCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                            rows += myAdapter.Update(ds, ds.Tables[i].TableName);

                        }
                    }
                }
                myTrans.Commit();
                ds.AcceptChanges();
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                rows = 0;
                throw ex;
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                    myConn.Close();
            }
            return rows;
        }

        /// <summary>
        /// 更新DataSet数据源中指定列
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static int SaveDataSetBySpecifyCol(DataSet ds, Dictionary<string, string> specifyColNames)
        {
            int rows = 0;
            SqlConnection myConn = null;
            SqlTransaction myTrans = null;
            SqlCommand myCommand = null;
            try
            {
                myConn = new SqlConnection(ConnectionString);
                if (myConn.State == ConnectionState.Closed)
                    myConn.Open();
                myTrans = myConn.BeginTransaction();
                SqlDataAdapter myAdapter = null;
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    if (specifyColNames.ContainsKey(ds.Tables[i].TableName))
                    {
                        string _specifyCols = specifyColNames[ds.Tables[i].TableName];
                        if (string.IsNullOrEmpty(_specifyCols))
                        {
                            myCommand = new SqlCommand("select * from " + ds.Tables[i].TableName, myConn);
                            myAdapter = new SqlDataAdapter(myCommand);
                            myAdapter.SelectCommand.Transaction = myTrans;
                            SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                            myCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                            rows += myAdapter.Update(ds, ds.Tables[i].TableName);
                        }
                        else
                        {
                            DataTable _dt = ds.Tables[i].GetChanges();
                            myCommand = new SqlCommand();
                            myCommand.CommandText = "Select " + _specifyCols + " from " + ds.Tables[i].TableName;
                            myCommand.Connection = myConn;
                            myAdapter = new SqlDataAdapter(myCommand);
                            myAdapter.SelectCommand.Transaction = myTrans;
                            SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                            myCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
                            rows += myAdapter.Update(ds, ds.Tables[i].TableName);
                        }
                    }
                }
                myTrans.Commit();
                ds.AcceptChanges();
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                rows = 0;
                throw ex;
            }
            finally
            {
                if (myConn.State == ConnectionState.Open)
                    myConn.Close();
            }
            return rows;
        }
        /// <summary>
        /// 批量导入数据
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="aryTableName"></param>
        /// <returns></returns>
        public static bool SqlBulkCopy(DataSet ds)
        {
            SqlConnection myConn = new SqlConnection(ConnectionString);
            SqlTransaction trans = null;
            SqlCommand myCommand = null;
            bool result = false;
            try
            {
                //数据批量导入SqlBulkCopy
                myConn.Open();
                trans = myConn.BeginTransaction();
                myCommand = myConn.CreateCommand();
                myCommand.Transaction = trans;
                foreach (DataTable dt in ds.Tables)
                {
                    myCommand.CommandText = "truncate table " + dt.TableName;
                    myCommand.ExecuteNonQuery();
                }
                SqlBulkCopy sqlbulk = new SqlBulkCopy(myConn, SqlBulkCopyOptions.Default, trans);
                //SqlBulkCopy sqlbulk = new SqlBulkCopy(myConn);
                //目标数据库表名
                sqlbulk.BatchSize = 10000;
                sqlbulk.BulkCopyTimeout = 60;
                foreach (DataTable dt in ds.Tables)
                {
                    sqlbulk.ColumnMappings.Clear();
                    sqlbulk.DestinationTableName = dt.TableName;
                    //数据集字段索引与数据库字段索引映射
                    foreach (DataColumn col in dt.Columns)
                    {
                        sqlbulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }
                    //导入
                    sqlbulk.WriteToServer(dt);
                }
                trans.Commit();
                sqlbulk.Close();
                result = true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                myConn.Close();
                ds.Dispose();
            }
            return result;
        }
        #endregion

    }
}
